package com.jackstraw.dbtool.service.impl;

import com.jackstraw.dbtool.exception.MyException;
import com.jackstraw.dbtool.param.CommonParam;
import com.jackstraw.dbtool.service.IE2dService;
import com.jackstraw.dbtool.utils.DBToJavaParamTypeMap;
import com.jackstraw.dbtool.utils.DBUtil;
import com.jackstraw.dbtool.utils.ExcelUtil;
import com.jackstraw.dbtool.utils.MyStringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 所在包：com.jackstraw.e2d.service.impl
 * 文件名：
 * 版权：稻草人.
 * 项目：e2d
 * 功能描述：
 * 修改人：Xinglz
 * 修改时间：2020-01-15 10:34
 */
@Service
public class E2dServiceImpl implements IE2dService {

    @Override
    public void excelToDB(String sheetName, String titleList, String tableName) throws Exception {
        Map<String, String> fieldMap = getTitleToFieldMap(titleList);
        Workbook workbook = ExcelUtil.getWorkbook();
        Sheet sheet = workbook.getSheet(sheetName);
        Row row = sheet.getRow(0);

        // 获取字段顺数sql及类型
        Iterator<Cell> titleIterator = row.cellIterator();
        List<String> typeList = new ArrayList<>();
        Map<String, String> columnToTypeMap = DBUtil.getColumnToTypeMap(tableName);
        String fieldStr = "(";
        String indexStr = ",";
        int index = 0;
        while (titleIterator.hasNext()) {
            Cell next = titleIterator.next();
            String title = next.getStringCellValue();
            String field = fieldMap.get(title);
            if (MyStringUtil.isNotEmty(field)) {
                fieldStr = fieldStr + field + ",";
                // 生成类型列表
                String columnType = columnToTypeMap.get(field);
                String javaType = DBToJavaParamTypeMap.getJavaParamType(columnType);
                typeList.add(javaType);
                indexStr = indexStr + index + ",";
            } else {
                typeList.add("String");
            }
            index++;
        }
        fieldStr = fieldStr.substring(0, fieldStr.length()-1) + ")";

        // 获取excel中的数据，并生成值字符串
        String valueStr = "(";
        int i = 0;
        for (Row dataRow: sheet) {
            if (i > 0) {
                Iterator<Cell> dataCell = dataRow.cellIterator();
                int j = 0;
                while (dataCell.hasNext()) {
                    String currentIndex = "," + j + ",";
                    String javaType = typeList.get(j);
                    Cell cell = dataCell.next();
                    if (indexStr.contains(currentIndex)) {
                        // 获取单元格数据
                        String cellValue = getCellValue(cell);
                        // 根据数据类型处理单元格数据
                        cellValue = handleValue(cellValue, javaType);

                        valueStr = valueStr + cellValue + ",";
                    }
                    j++;
                }
                valueStr = valueStr.substring(0, valueStr.length()-1);
                valueStr = valueStr + "),(";
            }
            i++;
        }
        valueStr = valueStr.substring(0, valueStr.length() - 2);

        // 拼装sql语句
        String sql = "insert into " + tableName + fieldStr + " values" + valueStr;
        System.out.println(sql);
        // 执行插入sql语句
        Connection conn = DBUtil.getConn();
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.execute();
    }

    /**
     * 处理结果值
     * @param cellValue
     * @param javaType
     * @return
     */
    private String handleValue(String cellValue, String javaType) {
        if (MyStringUtil.isEmpty(cellValue))
            return "null";
        switch (javaType) {
            case "String":
                cellValue = "'" + cellValue + "'";
                break;
            case "Integer":
            case "BigDecimal":
                break;
            default:
                cellValue = "'" + cellValue + "'";
                break;
        }
        return cellValue;
    }

    /**
     * 获取单元格数据
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-M-d H:m:s");
        SimpleDateFormat fmt2 = new SimpleDateFormat("yyyy-M-d");
        String cellValue = "";
        switch(cell.getCellType()) {
            case Cell.CELL_TYPE_STRING: //文本
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                if(DateUtil.isCellDateFormatted(cell)) {
                    try {
                        cellValue = fmt.format(cell.getDateCellValue()); //日期型
                    } catch (Exception e) {
                        cellValue = fmt2.format(cell.getDateCellValue()); // 日期格式2
                    }
                }
                else {
                    cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: //空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: //错误
                cellValue = "";
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = "";
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    /**
     * 根据上传文件获取可操作excel对象
     * @param file
     * @return
     */
    public Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.getMessage();
        }
        return workbook;
    }

    /**
     * 根据标题字符串获取Map
     * @param titleList
     * @return
     */
    private Map<String, String> getTitleToFieldMap(String titleList) {
        Map<String, String> map = new HashMap<>();
        if (MyStringUtil.isEmpty(titleList))
            throw new MyException("没有表头数据！");
        String[] titleArr = titleList.split(";");
        for (String titleItem: titleArr) {
            if (MyStringUtil.isNotEmty(titleItem)) {
                String[] items = titleItem.split(",");
                if (items.length < 2) {
                    System.out.println(CommonParam.LOG_REMARK_PREFIX + "数据异常：" + titleItem);
                    throw new MyException("数据异常，请联系管理员");
                }
                String field = items[0];
                String title = items[1];
                if (MyStringUtil.isEmpty(field) || MyStringUtil.isEmpty(title))
                    throw new MyException("数据异常，请联系管理员");
                map.put(title.trim(), field.trim());
            }
        }
        return map;
    }
}
